package sql;
import info.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import tool.*;

import javax.swing.*;


public class Connect implements sql{
    private static Connection connect; // 声明Connection对象
    private  Format fm = new Format();//时间格式转换
    private static final String MYSQL_URL = "jdbc:mysql://192.168.3.128:3306/demo";
    private static final String MYSQL_USER = "root";
    private static final String MYSQL_PASSWORD = "123456";
    private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    public  Connect() { // 建立返回值为Connection的方法

        try { // 通过访问数据库的URL获取数据库连接对象
            System.out.println("Connecting to database...");
            connect = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
            e.printStackTrace();
        }
    }

    public User Login(LoginForm loginForm) throws SQLException {
        String query = "SELECT * FROM users WHERE number = ? and role = ? ";
        try (PreparedStatement pstmt = connect.prepareStatement(query)){
            System.out.println("loginForm.getNumber()"+loginForm.getNumber());
            System.out.println("loginForm.getPwd()"+loginForm.getPwd());
            System.out.println("loginForm.getRole()"+loginForm.getRole());
            pstmt.setString(1, loginForm.getNumber());
            pstmt.setString(2, loginForm.getRole());
            ResultSet rs = pstmt.executeQuery();
            //判断该角色下是否存在该用户
            if(rs.next()){
                //判断密码是否正确
                if(rs.getString("pwd").equals(loginForm.getPwd())){
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setNumber(rs.getString("number"));
                    user.setName(rs.getString("name"));
                    user.setPwd(rs.getString("pwd"));
                    user.setRole(rs.getString("role"));
                    return user;
                }else{
                    JOptionPane.showMessageDialog(null, "密码错误", "错误 ", JOptionPane.ERROR_MESSAGE);
                }

            }else{
                JOptionPane.showMessageDialog(null, "用户不存在", "错误 ", JOptionPane.ERROR_MESSAGE);
                return null;
            }
        }catch (SQLException e) {
            e.printStackTrace();; // 处理异常，例如记录日志或显示错误信息
        }
        return null;
    }

    @Override
    public int Register(RegistrationForm RgsForm) throws SQLException {
        String query = "INSERT INTO users (number,name, pwd,role) VALUES (?,?,?,?)";
        try (PreparedStatement pstmt = connect.prepareStatement(query)){
            pstmt.setString(1, RgsForm.getNumber());
            pstmt.setString(2, RgsForm.getName());
            pstmt.setString(3, RgsForm.getPwd());
            pstmt.setString(4, RgsForm.getRole());
           int result = pstmt.executeUpdate();
           return result;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int addSubscribe(SubscribeForm SubForm) {
       String query = "INSERT INTO subscribe (number,name,reason,startTime,endTime,type) VALUES (?,?,?,?,?,?)";
        try(PreparedStatement pstmt = connect.prepareStatement(query)){
            pstmt.setString(1, SubForm.getNumber());
            pstmt.setString(2, SubForm.getName());
            pstmt.setString(3, SubForm.getReason());
            // 使用 setTimestamp 而不是 setDate
            pstmt.setTimestamp(4, fm.FormatDate(SubForm.getStartDate()));
            pstmt.setTimestamp(5, fm.FormatDate(SubForm.getEndDate()));
            pstmt.setString(6, SubForm.getType());
            int result = pstmt.executeUpdate();
            return result;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    };
    public SubscribeForm[] getSubscribe(User user){
        SubscribeForm[] subs=null;
        String query = "SELECT subscribe.id,subscribe.type, subscribe.number, subscribe.name, subscribe.reason, subscribe.startTime, subscribe.endTime, subscribeState.message FROM subscribe, subscribeState WHERE subscribe.number = ? AND subscribe.statue = subscribeState.code ";
        //
        try(PreparedStatement pstmt = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)){
            pstmt.setString(1, user.getNumber());
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()) {
                //获取行数
                rs.last();
                int rowCount = rs.getRow();
                subs = new SubscribeForm[rowCount];
                rs.first();
                int index = 0;
                do {
                    SubscribeForm sub = new SubscribeForm();
                    sub.setId(Long.parseLong(rs.getString("id")));
                    sub.setType(rs.getString("type"));
                    sub.setNumber(rs.getString("number"));
                    sub.setName(rs.getString("name"));
                    sub.setReason(rs.getString("reason"));
                    sub.setStartDate(fm.FormatDate(rs.getDate("startTime")));
                    sub.setEndDate(fm.FormatDate(rs.getDate("endTime")));
                    sub.setMessage(rs.getString("message"));
                    subs[index++] = sub;
                } while (rs.next());
                for (SubscribeForm s : subs) {
                    System.out.println("text:" + s.getType());
                    System.out.println("text:" + s.getReason());
                    System.out.println("text:" + s.getStartDate());
                    System.out.println("text:" + s.getEndDate());
                    System.out.println("text:" + s.getStatue());
                }
                return subs;
            }else{
                return subs;
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        }


    }
    public  SubscribeForm[] getSubscribe(User user, Date start, Date end){
         SubscribeForm[] subs=null;
         String query = "SELECT subscribe.id,subscribe.type, subscribe.number, subscribe.name, subscribe.reason, subscribe.startTime, subscribe.endTime, subscribeState.message FROM subscribe,subscribeState WHERE number = ? AND startTime >= ? AND startTime <= ? AND subscribe.statue = subscribeState.code ";

//         String query = "SELECT subscribe.id,subscribe.type, subscribe.number, subscribe.name, subscribe.reason, subscribe.startTime, subscribe.endTime, subscribeState.message FROM subscribe,subscribeState WHERE number = ? AND startTime >= ? AND startTime <= ? AND subscribe.statue = subscribeState.code order by ?";
         try(PreparedStatement  pstmt = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)){
             pstmt.setString(1, user.getNumber());
             pstmt.setTimestamp(2, fm.FormatDate(start));
             pstmt.setTimestamp(3, fm.FormatDate(end));
             ResultSet rs = pstmt.executeQuery();
             if(rs.next()){
                 //获取行数
                 rs.last();
                 int rowCount = rs.getRow();
                 subs = new SubscribeForm[rowCount];
                 rs.first();
                 int index = 0;
                 do {
                     SubscribeForm sub = new SubscribeForm();
                     sub.setId(Long.parseLong(rs.getString("id")));
                     sub.setType(rs.getString("type"));
                     sub.setNumber(rs.getString("number"));
                     sub.setName(rs.getString("name"));
                     sub.setReason(rs.getString("reason"));
                     sub.setStartDate(fm.FormatDate(rs.getDate("startTime")));
                     sub.setEndDate(fm.FormatDate(rs.getDate("endTime")));
                     sub.setMessage(rs.getString("message"));
                     subs[index++]=sub;
                 }while (rs.next());
                 for(SubscribeForm s:subs){
                     System.out.println("test:"+s.getType());
                     System.out.println("test:"+s.getReason());
                     System.out.println("test:"+s.getStartDate());
                     System.out.println("test:"+s.getEndDate());
                     System.out.println("test:"+s.getStatue());
                 }
                 return subs;
             }else{
                 return subs;
             }

         } catch (Exception e) {
             throw new RuntimeException(e);
         }
    }
    public   boolean deleteSubscribe(String id,User user){
        if(user!=null){
            String query = "DELETE FROM subscribe WHERE id = ?  ";
            try(PreparedStatement pstmt = connect.prepareStatement(query)){
                pstmt.setString(1, id);
                return pstmt.executeUpdate()>0;
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }

        return false;
    }
    public static void close() throws SQLException {
        if (connect != null) {
            connect.close(); //关闭连接
            System.out.println("关闭数据库连接");
        }
    }
}
